package codebush.service.impl;

import java.sql.Blob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.util.Assert;

import codebush.domain.Article;
import codebush.domain.Comment;
import codebush.domain.Users;
import codebush.service.Get;
import codebush.service.support.rowMapper.RowMapperForArticle;
import codebush.service.support.rowMapper.RowMapperForComment;
import codebush.service.support.rowMapper.RowMapperForUsers;

/**
 * used for database query 2008-4-2
 * 
 * @author Fution Bai Copyright 2007
 */

public class GetImpl extends SimpleJdbcDaoSupport implements Get {

	/**
	 * 分页查询中的limit的两个参数
	 */
	public static int DEFAULT_OFFSET = 0;
	public static int DEFAULT_ROW_COUNT = 20;

	/**
	 * get the user's id by email.If the email is not registered, return 0
	 * 
	 * @param email
	 * @return
	 */
	public int getUserId(String email) {
		if (whetherRegistered(email)) {
			String sql = "select id from users where email=?";
			Object[] args = { email };
			int id = getJdbcTemplate().queryForInt(sql, args);
			return id;
		}
		return 0;
	}

	/**
	 * define whether the email has been registered
	 * 
	 * @param email
	 * @return true if registered, else false
	 */
	public boolean whetherRegistered(String email) {
		Assert.notNull(email);
		String sql = "select email from users where email=?";
		PreparedStatement ps;
		try {
			ps = getJdbcTemplate().getDataSource().getConnection().prepareStatement(sql);
			ps.setString(1, email);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * get a specific user's information
	 */
	public Users getUser(int userId) {
		String sql = "select * from users where id=?";
		Object[] args = { userId };
		SqlRowSet srs = getJdbcTemplate().queryForRowSet(sql, args);
		return RowMapperForUsers.getInstance().mapRow(srs);
	}

	/**
	 * get an article
	 * 
	 * @param articleId
	 * @param userId
	 * @return
	 */
	public Article getArticle(Long articleId) {
		Article article = new Article();
		String sql = "select * from v_articlen where id = " + articleId;
		SqlRowSet srs = getJdbcTemplate().queryForRowSet(sql);
		article = RowMapperForArticle.getInstance().mapRow(srs).get(0);
		return article;
	}

	/**
	 * get a specific user's articles(no replies)
	 * 
	 * @param userId
	 * @return
	 */
	public List<Article> getArticlesByUserId(int userId, int offSet, int rowCount) {
		List<Article> list = new ArrayList<Article>();
		String sql = "select id, title from article where user_id=? limit "+offSet+","+rowCount;
		Object[] args = { userId };
		SqlRowSet srs = getJdbcTemplate().queryForRowSet(sql, args);
		String[] columns = { "id", "title" };
		list = RowMapperForArticle.getInstance().mapRowForSpecificColumns(srs, columns, 0);
		return list;
	}

	public List<Article> getArticlesByUserId(int userId) {
		return getArticlesByUserId(userId, DEFAULT_OFFSET, DEFAULT_ROW_COUNT);
	}
	
	public List<Article> getArticlesByUserId(int userId, int offset) {
		return getArticlesByUserId(userId, offset, DEFAULT_ROW_COUNT);
	}
	
	/**
	 * get the number of a user's article
	 */
	public int getTotalNumOfArticlesByUserId(int userId) {
		String sql = "select count(*) from article where user_id=?";
		Object[] args = { userId };
		int totalNum = getJdbcTemplate().queryForInt(sql, args);
		return totalNum;
	}
	
	/**
	 * get a specific user's all replies
	 * 
	 * @param userId
	 * @return
	 */
	public List<Comment> getCommentsByUserId(int userId) {
		List<Comment> list = new ArrayList<Comment>();
		String sql = "select * from v_commentn where subject_id!=0 and user_id=?";
		Object[] args = { userId };
		SqlRowSet srs = getJdbcTemplate().queryForRowSet(sql, args);
		list = RowMapperForComment.getInstance().mapRow(srs);
		return list;
	}

	/**
	 * get a specified article's all replies
	 * 
	 * @param articleId
	 * @return
	 */
	public List<Comment> getCommentsByAId(Long articleId) {
		List<Comment> list = new ArrayList<Comment>();
		String sql = "select * from v_commentn where subject_id='" + articleId + "'";
		SqlRowSet src = getJdbcTemplate().queryForRowSet(sql);
		list = RowMapperForComment.getInstance().mapRow(src);
		return list;
	}

	/**
	 * select the most featured subjects,filtered by type, userId...
	 * 如果userID==0，则不是与特定用户相关的
	 */
	public Object top(String type, String orderBy, int userId) {
		if (type.equals("article") && userId == 0) {
			String sql = "select id, title, content, post_time from article where post_time >= CURRENT_DATE()-21 order by " + orderBy + " DESC";
			String[] columns = { "id", "title", "content", "post_time" };
			SqlRowSet srs = getJdbcTemplate().queryForRowSet(sql, null);
			return RowMapperForArticle.getInstance().mapRowForSpecificColumns(srs, columns, 0);
		} else if (type.equals("article") && userId > 0) {
			String sql = "select id, title, content, post_time from article where post_time >= CURRENT_DATE()-21 and user_id=" + userId + " order by " + orderBy + " DESC";
			String[] columns = { "id", "title", "content", "post_time" };
			SqlRowSet srs = getJdbcTemplate().queryForRowSet(sql, null);
			return RowMapperForArticle.getInstance().mapRowForSpecificColumns(srs, columns, 0);
		}
		return null;
	}

	public List<Comment> getLatestCommentsByUserId(int userId) {
		List<Comment> list = new ArrayList<Comment>();
		String sql = "select * from v_commentn where subject_id!=0 and user_id=? order by post_time DESC";
		Object[] args = { userId };
		SqlRowSet srs = getJdbcTemplate().queryForRowSet(sql, args);
		list = RowMapperForComment.getInstance().mapRow(srs);
		return list;
	}

	public Object getUserLogoPictureByUserId(int userId) {
		String sql = "select picture from users where id=?";
		Object[] args = { userId };
		Object p = getJdbcTemplate().queryForObject(sql, args, Blob.class);
		return p;
	}

	public int getArticleIdByLinkAlias(String linkAlias) {
		String sql = "select id from article where link_alias=?";
		Object[] args = { linkAlias };
		int aid = getJdbcTemplate().queryForInt(sql, args);
		return aid;
	}

}
